Snowflake×dbt特有の設定をまとめてみる #SnowflakeDB #dbt

※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の24日目の記事となります。

さがらです。

dbtはDWH上のデータパイプライン構築に役立つツールですが、各DWH特有の機能に応じた設定も用意されています。その中でもSnowflakeの機能に応じたdbtの設定に着目し、まとめていきたいと思います。

元ネタ

dbtの公式Docに「Snowflake configurations」というページがあります。こちらの内容をまとめていきます。

dbtから構築するテーブルの種類

dbtはDWH上のデータパイプラインを担うツールですので、DWH上にテーブルやビューを構築できます。

この時、テーブルに関してSnowflakeには3種類のテーブルがあります。(下図は公式Docより)

さて、この上でdbtではどの種類のテーブルが作られるかですが、デフォルトでは全てTransientテーブルで作られます。

Transientテーブルの特徴は以下です。

• 明示的にDROPするまでは消えない
• タイムトラベルはEnterpriseプランであっても最大1日まで（デフォルト1日）
• タイムトラベル期間後のリカバリ対応(フェイルセーフ)はできない

一見タイムトラベルが1日だったりフェイルセーフが出来ないなど不安を感じてしまうかもしれませんが、dbtはmodelとして定義しておけば、ソーステーブルが消えない限りすぐに再構築可能です。

かつ、タイムトラベルやフェイルセーフの対象期間となっている間はストレージコストが発生します。ストレージコストを削減する意味でも、dbtを介して作られるテーブルはTransientで充分だと感じます。

dbtでPermanentテーブルを作りたい

しかしそうは言っても、すぐにリカバリ出来るようにタイムトラベルの期間を一定期間持たせておきたく、Permanentテーブルを使いたい、という場面もあると思います。

こちらについて、大きく2つ方法があります。

プロジェクト全体に適用させる方法

対象のプロジェクトのdbt_project.ymlにおいて、modelsパラメータの直下に+transient: falseと入れてあげればOKです。この設定により、modelsの直下に指定することで、対象プロジェクトのテーブルを生成するmodelの場合は、全てPermanentテーブルを生成します。

name: my_project

...

models:
  +transient: false
  my_project:
    ...

modelごとに適用させる方法

一方で、あるmodelだけにPermanentテーブルを適用させたい場合もあると思います。（どちらかというとこっちのほうが多い気もします。）

その場合は、下記のようにconfigを設定すれば、対象のmodelはPermanentテーブルを生成するようになります。

{{ config(materialized='table', transient=false) }}

select * from ...

クエリタグ

Snowflakeには「クエリタグ」という機能があり、セッション内で実行されるクエリおよびその他のSQLステートメントのタグ付けに使用出来ます。コンソール画面での履歴タブやQUERY_HISTORYビューから過去のクエリを検索する際、クエリタグが付いていると目的のクエリを検索しやすくなります。

※下記ページのQUERY_TAGというパラメータです。

このクエリタグの付与を、dbtから発行されたクエリに対して行うことが出来ます。

dbtを介したクエリタグの付与

dbtを介したクエリタグの付与について、大きく3つ方法があります。

model全体へ適用する方法

対象のプロジェクトのdbt_project.ymlのmodelsパラメータ内の各リソースパスに対して、+query_tagの定義をすれば、OKです。リソースパスとは対象のmodelを保持するフォルダのことなので、marts用のタグ、staging用のタグ、のように切り分けることが可能です。

models:
  [<resource-path>](resource-path):
    +query_tag: dbt_special

modelごとに適用する方法

各modelごとに適用させたい場合は、下記のように{{ config }}の中でquery_tagの定義をすればOKです。

{{ config(
    query_tag = 'dbt_special'
) }}

select ...

マクロを用いる方法

マクロを記述して独自のルールでクエリタグを付与することも可能です。

下記の例は、対象のmodelが実行された時に、そのmodel名をクエリタグとして記録してくれるマクロです。 （この記事でも触れています。）

  {% macro set_query_tag() -%}
  {% set new_query_tag = model.name %} 
  {% if new_query_tag %}
    {% set original_query_tag = get_current_query_tag() %}
    {{ log("Setting query_tag to '" ~ new_query_tag ~ "'. Will reset to '" ~ original_query_tag ~ "' after materialization.") }}
    {% do run_query("alter session set query_tag = '{}'".format(new_query_tag)) %}
    {{ return(original_query_tag)}}
  {% endif %}
  {{ return(none)}}
{% endmacro %}

incremental strategy

まず、dbtには出力先のテーブルに対して増分更新を行うことが可能なのです。この時の増分更新の処理方法をincremental_strategyというパラメータで定義することが可能です。(詳細はこちらのDocをご覧ください。)

このとき、Snowflakeではデフォルトでmergeコマンドが動作します。

しかし、Snowflakeのmergeコマンドは、modelファイルで指定したunique_keyが実際にユニークでない場合、「nondeterministic merge」というエラーを返して失敗します。

このエラーの対処方法として、modelのincremental_strategyパラメータをdelete+insertと設定することでこのエラーを回避できるようになります。

incremental_strategyの設定方法

incremental_strategyを設定する方法は、大きく2つあります。※それぞれの挙動にどういった違いがあるかは、別途ブログを書く予定です。

model全体へ適用する方法

dbt_project.ymlファイルのmodelsパラメータで、+incremental_strategyを定義すればOKです。

models:
  +incremental_strategy: "delete+insert"

modelごとに適用する方法

各modelごとに適用させたい場合は、下記のように{{ config }}の中で+incremental_strategyの定義をすればOKです。

{{
  config(
    materialized='incremental',
    unique_key='date_day',
    incremental_strategy='delete+insert',
    ...
  )
}}

select ...

クラスタリングの設定

Snowflakeでは、マイクロパーティションという独自の形式でデータを保持しており、クエリの内容に応じて必要なマイクロパーティションだけを剪定するため、データのスキャン量を抑える仕組みができています。このマイクロパーティションに分ける工程はSnowflake側がよしなに判定して行ってくれるため、ユーザーは特に意識することはありません。そのため、ナチュラルデータクラスタリングと呼ばれています。

しかし、ナチュラルデータクラスタリングによってユーザーの意図せぬ形でデータが保持されている場合、データスキャン時に必要以上のマイクロパーティションをスキャンしてしまい、TB以上の大規模なデータだとクエリ実行時間の遅延に繋がる可能性もあります。

こんな時、ユーザーが指定したカラムをクラスタリングキーとして指定する機能をSnowflakeは用意しています。そして、このユーザーがクラスタリングキーを指定することを、dbtを介して行うことも可能です。

※このあたりのクラスタリングの説明については下記の記事もぜひご覧ください。

dbtを介してクラスタリングキーを設定する方法

modelを定義する際、{{config()}}でcluster_byというパラメータでクラスタリングキーを指定してあげればOKです。

下記は具体的なmodelファイルのサンプルとなります。ここではsession_startというカラムをクラスタリングキーとして設定していますね。

{{
  config(
    materialized='table',
    cluster_by=['session_start']
  )
}}

select
  session_id,
  min(event_time) as session_start,
  max(event_time) as session_end,
  count(*) as count_pageviews

from {{ source('snowplow', 'event') }}
group by 1

そして、このmodelが実行される際は以下のクエリにコンパイルされて実行されます。

create or replace table my_database.my_schema.my_table as (

  select * from (
    select
      session_id,
      min(event_time) as session_start,
      max(event_time) as session_end,
      count(*) as count_pageviews

    from {{ source('snowplow', 'event') }}
    group by 1
  )

  -- this order by is added by dbt in order to create the
  -- table in an already-clustered manner.
  order by session_start

);

 alter table my_database.my_schema.my_table cluster by (session_start);

クラスタリングの自動化

クラスタリングキーを定義されたテーブルは、データの更新が行われると必要に応じて再度クラスタリング処理を実施します。Snowflakeは自動でこの再クラスタリングを行ってくれるのですが、これはdbtを介してクラスタリングキーを設定した場合でも同様です。

※こちらのDocを見ると、手動クラスタリング機能はすべてのアカウントで廃止されているようですね。

dbtが使用するウェアハウス

dbtが使用するデフォルトのウェアハウスは、Snowflake接続用のプロファイルで設定されたものが使用されます。

しかしmodelによって処理対象のデータ量が異なる場合、サイズが異なるウェアハウスを使いたい、というケースがあるかもしれません。そんなとき、dbt側から使用するウェアハウス名を指定することが可能です。

dbtから使用するウェアハウスを指定する方法

dbt_project.ymlファイル内で、+snowflake_warehouseパラメータを使用したいウェアハウス名と共に指定すればOKです。

以下、1つ例を載せておきます。

事前に下図のように、XSサイズのXS_WHと、MサイズのMEDIUM_WH、2つのウェアハウスを定義しておくとします。

この上で、dbt_project.ymlファイル内で下記のように定義しておきます。

• 基本的にはXS_WHをクエリ実行時のウェアハウスとして使用する
• martsフォルダ内のmodelのクエリ実行時には、MEDIUM_WHのウェアハウスを使用する

※中略

models:
  +snowflake_warehouse: "XS_WH"
  sagara_dbt_workshop:
      staging:
          schema: staging
          materialized: table
      marts:
          schema: marts
          materialized: table
          +snowflake_warehouse: "MEDIUM_WH"

このとき、int_stock_histroy_major_currencyというmodelを実行してみます。以下5つのmodelが実行され、左2つはstagingフォルダ、右3つはmartsフォルダにて定義されているmodelです。

コマンドラインにdbt run -m +int_stock_history_major_currencyと入れて実行してみます。

下図が、上図のリネージに沿って実行されたクエリ履歴です。途中でUSE WAREHOUSEコマンドを使ってウェアハウスが変更されていることがわかります。

一方で、このdbt projectのプロファイルに設定したウェアハウスPC_DBT_WHに都度切り替わっていることも確認できました。 なぜこの事象が発生したかはわかっていないのですが、dbt projectのプロファイルとして設定したウェアハウスを基本的に使うようにし、必要に応じて別のウェアハウスに切り替える、という運用がひとまずは良さそうと感じました。

（dev環境ではテストデータだけなので小さめのウェアハウス、prod環境ではすべてのデータを対象とするので大きめのウェアハウス、といった形で切り替えたいケースはあると思うので、もう少しこの機能は検討していきたいですね…）

COPY GRANTS

COOY GRANTSとは、CREATE TABLEコマンドのオプションの1つです。このオプションが指定されていると、対象のテーブルを元にして以下のクエリが実行されて新しいテーブルが作られた時、対象テーブルのアクセス権を保持して作成されます。

• CREATE OR REPLACE TABLE
• CREATE TABLE ... LIKE
• CREATE TABLE ... CLONE

※下記DocのCOPY GRANTSにより詳細に記載されています。

このCOPY GRANTSのオプション設定を、dbtを介したテーブル生成でも行うことが可能です。

COPY GRANTSの設定方法

dbtで設定するには、dbt_project.yml内のmodelsパラメータにおいて、+copy_grants: trueと定義してあげればOKです。※デフォルト値はfalseです。

models:
  +copy_grants: true

Secure views

Snowflakeには、Secure Viewというビューに定義されたSELECT文を相手に公開せずビューが返す結果だけを提供する事が出来る機能があります。

※Secure Viewについてより詳しくはこちらをご覧ください。

このSecure Viewを、dbtを介して生成することが可能です。

Secure Viewの生成方法

dbt_project.yml内で、対象となるmodelを持つリソースパスの中で、+materialized: viewと+secure: trueを定義してあげればOKです。

以下のサンプルでは、sensitive/フォルダー内のmodelをSecure Viewとして生成するように定義しています。

name: my_project
version: 1.0.0

models:
  my_project:
    sensitive:
      +materialized: view
      +secure: true

次回

Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の25日目では、本アドベントカレンダーのまとめ記事を執筆します。お楽しみに！